<?php
/* -------------------------------------------------------------------------------------
* 	ID:						$Id: class.sales_report.php 11 2013-09-21 11:43:11Z phone.mueller@googlemail.com $
* 	Letzter Stand:			$Revision: 11 $
* 	zuletzt geaendert von:	$Author: peter $
* 	Datum:					$Date: 2013-09-21 11:43:11 +0000 (Sat, 21 Sep 2013) $
*
* 	SEO:mercari by Siekiera Media
* 	http://www.seo-mercari.de
*
* 	Copyright (c) since 2011 SEO:mercari
* --------------------------------------------------------------------------------------
* 	based on:
* 	(c) 2000-2001 The Exchange Project  (earlier name of osCommerce)
* 	(c) 2002-2003 osCommerce - www.oscommerce.com
* 	(c) 2003     nextcommerce - www.nextcommerce.org
* 	(c) 2005     xt:Commerce - www.xt-commerce.com
*
* 	Released under the GNU General Public License
* ----------------------------------------------------------------------------------- */

defined( '_VALID_XTC' ) or die( 'Direct Access to this location is not allowed.'); 

class sales_report {
var $mode, $globalStartDate, $startDate, $endDate, $actDate, $showDate, $showDateEnd, $sortString, $status, $outlet, $db;

	function __construct($mode, $startDate = 0, $endDate = 0, $sort = 0, $statusFilter = 0, $filter = 0,$payment = 0) {
		global $db;

		$this->mode = $mode;
		$this->tax_include = DISPLAY_PRICE_WITH_TAX;
		$this->db = $db;
		$this->statusFilter = $statusFilter;
		$this->paymentFilter = $payment;     

		$first = $this->db->db_query("SELECT UNIX_TIMESTAMP(MIN(date_purchased)) AS first FROM ".TABLE_ORDERS);
		
		$this->globalStartDate = mktime(0, 0, 0, date("m", $first->fields['first']), date("d", $first->fields['first']), date("Y", $first->fields['first']));
		
		$outResp = $this->db->db_query("SELECT * FROM ".TABLE_ORDERS_STATUS." WHERE language_id = '".$_SESSION['languages_id']."'");
		$i = 0;
		while (!$outResp->EOF) {
			$status[$i] = $outResp->fields;
			$i++;
			$outResp->MoveNext();
		}
		$this->status = $status;
		
		if ($startDate == 0 OR $startDate < $this->globalStartDate)
			$this->startDate = $this->globalStartDate;
		else
			$this->startDate = $startDate;

		if ($this->startDate > mktime(0, 0, 0, date("m"), date("d"), date("Y")))
			$this->startDate = mktime(0, 0, 0, date("m"), date("d"), date("Y"));
		
		if ($endDate > mktime(0, 0, 0, date("m"), date("d") + 1, date("Y")))
			$this->endDate = mktime(0, 0, 0, date("m"), date("d") + 1, date("Y"));
		else
			$this->endDate = $endDate;

		if ($this->endDate < $this->startDate + 24 * 60 * 60)
			$this->endDate = $this->startDate + 24 * 60 * 60;
		
		$this->actDate = $this->startDate;

		$this->queryOrderCnt = "SELECT COUNT(o.orders_id) AS order_cnt FROM ".TABLE_ORDERS." o";

		$this->queryItemCnt = "SELECT 
									o.orders_id, 
									op.products_id as pid, 
									op.orders_products_id, 
									op.products_name as pname,
									op.products_model AS pmodel, 
									SUM(op.products_quantity) AS pquant, 
									SUM(op.final_price/o.currency_value) AS psum,
									op.products_tax AS ptax 
								FROM 
									".TABLE_ORDERS." o,
									".TABLE_ORDERS_PRODUCTS." op
								WHERE
									o.orders_id = op.orders_id";

		$this->queryAttr = "SELECT 
								COUNT(op.products_id) AS attr_cnt, 
								o.orders_id,
								opa.orders_products_id,
								opa.products_options,
								opa.products_options_values,
								opa.options_values_price,
								opa.price_prefix
							FROM 
								".TABLE_ORDERS_PRODUCTS_ATTRIBUTES." opa,
								".TABLE_ORDERS." o,
								".TABLE_ORDERS_PRODUCTS." op
							WHERE
								o.orders_id = opa.orders_id
							AND
								op.orders_products_id = opa.orders_products_id";

		$this->queryShipping = "SELECT
									SUM(ot.value/o.currency_value) AS shipping
								FROM 
									".TABLE_ORDERS." o,
									".TABLE_ORDERS_TOTAL." ot
								WHERE 
									ot.orders_id = o.orders_id
								AND
									ot.class = 'ot_shipping'";
	
		switch ($sort) {
			case '0':
				$this->sortString = "";
				break;

			case '1':
				$this->sortString = " ORDER BY pname asc ";
				break;

			case '2':
				$this->sortString = " ORDER BY pname desc";
				break;

			case '3':
				$this->sortString = " ORDER BY pquant asc, pname asc";
				break;

			case '4':
				$this->sortString = " ORDER BY pquant desc, pname asc";
				break;

			case '5':
				$this->sortString = " ORDER BY psum asc, pname asc";
				break;

			case '6':
				$this->sortString = " ORDER BY psum desc, pname asc";
				break;
		}
	}

	function getNext() {
		switch ($this->mode) {
			// yearly
			case '1':
				$sd = $this->actDate;
				$ed = mktime(0, 0, 0, date("m", $sd), date("d", $sd), date("Y", $sd) + 1);
				break;

			// monthly
			case '2':
				$sd = $this->actDate;
				$ed = mktime(0, 0, 0, date("m", $sd) + 1, 1, date("Y", $sd));
				break;

			// weekly
			case '3':
				$sd = $this->actDate;
				$ed = mktime(0, 0, 0, date("m", $sd), date("d", $sd) + 7, date("Y", $sd));
				break;

			// daily
			case '4':
				$sd = $this->actDate;
				$ed = mktime(0, 0, 0, date("m", $sd), date("d", $sd) + 1, date("Y", $sd));
				break;
		}

		if($ed > $this->endDate)
			$ed = $this->endDate;
		
		$filterString = "";
		if($this->statusFilter > 0)
			$filterString .= " AND o.orders_status = ".$this->statusFilter." ";
	
		if(!is_numeric($this->paymentFilter))
			$filterString .= " AND o.payment_method =".$this->db->db_prepare($this->paymentFilter)." ";
		
		$items = $this->db->db_query($this->queryItemCnt." 
										AND 
											o.date_purchased >= ".$this->db->db_prepare(date("Y-m-d H:i:s", $sd))." 
										AND 
											o.date_purchased < ".$this->db->db_prepare(date("Y-m-d H:i:s", $ed)).$filterString." 
										GROUP BY 
											pid ".$this->sortString);
		
		$this->actDate = $ed;
		$this->showDate = $sd;
		$this->showDateEnd = $ed - 60 * 60 * 24;

		$cnt = 0;
		$itemTot = 0;
		$sumTot = 0;
		
		while(!$items->EOF) {
			if(!empty($items->fields['pquant']))
				$price = $items->fields['psum'] / $items->fields['pquant'];
			
			$order = $this->db->db_query($this->queryOrderCnt." 
											WHERE 
												o.date_purchased >= '".date("Y-m-d H:i:s", $sd)."' 
											AND 
												o.date_purchased < '".date("Y-m-d H:i:s", $ed)."'".$filterString);
			
			$shipping = $this->db->db_query($this->queryShipping." 
												AND 
													o.date_purchased >= '".date("Y-m-d H:i:s", $sd)."'
												AND 
													o.date_purchased < '".date("Y-m-d H:i:s", $ed)."'".$filterString);
			
			$attr = $this->db->db_query($this->queryAttr." 
												AND 
													o.date_purchased >= ".$this->db->db_prepare(date("Y-m-d H:i:s", $sd))." 
												AND 
													o.date_purchased < ".$this->db->db_prepare(date("Y-m-d H:i:s", $ed))." 
												AND 
													op.products_id = ".$items->fields['pid'].$filterString." 
												GROUP BY 
													opa.products_options_values 
												ORDER BY 
													op.orders_products_id");
			
			$i = $attr->_numOfRows;
			
			if ($i > 0) {
				$price2 = 0;
				$price3 = 0;
				$option = array();
				$k = -1;
				$ord_pro_id_old = 0;
				while(!$attr->EOF) {
					if ($attr->fields['price_prefix'] == "-") {
						$price2 += (-1) *  $attr->fields['options_values_price'];
						$price3 = (-1) * $attr->fields['options_values_price'];
						$prefix = "-";
					} else {
						$price2 += $attr->fields['options_values_price'];
						$price3 = $attr->fields['options_values_price'];
						$prefix = "+";
					}
					$ord_pro_id = $attr->fields['orders_products_id'];
					if ( $ord_pro_id != $ord_pro_id_old) {
						$k++;
						$l = 0;
						$option[$k]['quant'] = $attr->fields['attr_cnt'];
						$option[$k]['options'][0] = $attr->fields['products_options'];
						$option[$k]['options_values'][0] = $attr->fields['products_options_values'];
						if ($price3 != 0)
							$option[$k]['price'][0] = $price3;
						else
							$option[$k]['price'][0] = 0;

					} else {
						$l++;
						$option[$k]['options'][$l] = $attr->fields['products_options'];
						$option[$k]['options_values'][$l] = $attr->fields['products_options_values'];
						if ($price3 != 0)
							$option[$k]['price'][$l] = $price3;
						else
							$option[$k]['price'][$l] = 0;
					}
					$ord_pro_id_old = $ord_pro_id;
					$attr->MoveNext();
				}
				$resp[$cnt]['attr'] = $option;
			} else
				$resp[$cnt]['attr'] = '';

			$resp[$cnt]['pid'] = $items->fields['pid'];
			$resp[$cnt]['pname'] = $items->fields['pname'];
			$resp[$cnt]['pmodel'] = $items->fields['pmodel'];
			$resp[$cnt]['items_cnt'] = $items->fields['pquant'];
			$resp[$cnt]['price'] = $price;
			$resp[$cnt]['psum'] = $items->fields['pquant'] * $price;
			$resp[$cnt]['order'] = $order->fields['order_cnt'];			
			$resp[$cnt]['shipping'] = $shipping->fields['shipping'];
			
			$sumTot += $resp[$cnt]['psum'];
			$itemTot += $items->fields['pquant'];

			$resp[$cnt]['totsum'] = $sumTot;
			$resp[$cnt]['totitem'] = $itemTot;
			$cnt++;
			$items->MoveNext();
		}
		return $resp;
	}
}